
import cx_Oracle
import codecs
import string

zf2 = lambda x: string.zfill(str(x), 2)

class FetchAll:
    ''' '''
    def __init__(self, cursor):
        self.__cursor = cursor
    def __getitem__(self, index):
        # The indices had better be in order, because we're not
        # wasting time checking them.
        result = self.__cursor.fetchone()
        if result is not None:
            return result
        else:
            raise IndexError

def export( exTable
          , conStr={'usr':'', 'pwd':'', 'dsn':''}
          , getHeader=True
          , cDelimiter='^'):

    ''' export oracle table
    '''
    o = codecs.open(exTable + '.exported.txt', 'wb', 'utf-8')

    #con = cx_Oracle.connect(conString)
    con = cx_Oracle.connect(conStr['usr'], conStr['pwd'], conStr['dsn'])
    cur = con.cursor()
    cur.execute("select '--row--', a.* from %s a" % (exTable,))

    #http://code.activestate.com/recipes/577302-oracle-database-string-search-using-cx_oracle/
    if getHeader: # add column headers if requested
        cols = []
        for col in cur.description:
            cols.append(col[0])
            #print col[0], cDelimiter
            o.write(col[0].strip() + cDelimiter)
        o.write('\x0D\x0A')
        #print '\x0D'
        #o.writerow(cols)

    for r in FetchAll(cur):
        for i in r:
            #print i,
            #this worked for some tables 
            try:
                o.write(unicode(i).strip() + cDelimiter)
            except:
                o.write(i.strip() + cDelimiter)
        #print '\x0D'
        o.write('\x0D\x0A')

    cur.close()
    con.close()
    o.close()

if 0: # testing routine

    from pepe.p_sql.oracle_export_table import *
    
    ip = '127.0.0.1' # tunneled to remote server
    port = 31521 # shifted to tunneled 31521, on 1521 is local XE instance
    sid = 'dtrslab3'
    dsn_tns = cx_Oracle.makedsn(ip, port, sid)
    usr = 'vpresovsky'
    pwd = '...'
    conString = {'usr': usr, 'pwd': pwd, 'dsn': dsn_tns}
    export('GIS0321.A_result_ZADPROVDOK_2columns', conString)
